#include "mysql.h"
#include <QDebug>

#include <QtSql/QSqlDatabase>
MySql::MySql() {
    qDebug()<<"mysql construct()!!";
    connect("127.0.0.1",3306,"root","tt751106","bear");
}

MySql::~MySql()
{
    qDebug()<<"mysql disconstruct()!!";

    disconnect();

}

bool MySql::connect(const QString &host, int port, const QString &user, const QString &password, const QString &database)
{
    db = QSqlDatabase::addDatabase("QMYSQL");
    qDebug() <<1111<< db.drivers();
    db.setHostName(host);
    db.setPort(port);
    db.setDatabaseName(database);
    db.setUserName(user);
    db.setPassword(password);

    if (!db.open()) {
        qDebug() << "Mysql Database connection failed:" << db.lastError().text();
        return false;
    }
    return true;
}

void MySql::disconnect()
{
    if (db.isOpen()) {
        db.close();
    }
}

bool MySql::query(const QString &sql)
{
    QSqlQuery query;
    return query.exec(sql);
}

bool MySql::insert(const QString &tableName, const QHash<QString, QVariant> &data)
{
    QString columns, values;
    for (auto it = data.cbegin(); it != data.cend(); ++it) {
        columns += it.key() + ",";
        values += ":" + it.key() + ",";
    }
    columns.chop(1);
    values.chop(1);

    QString sql = QString("INSERT INTO %1 (%2) VALUES (%3)").arg(tableName, columns, values);
    QSqlQuery query;
    query.prepare(sql);
    for (auto it = data.cbegin(); it != data.cend(); ++it) {
        query.bindValue(":" + it.key(), it.value());
    }
    return query.exec();
}

bool MySql::batchInsert(const QString &tableName, const QStringList &columns, const QList<QList<QVariant> > &dataList)
{
    if (columns.isEmpty() || dataList.isEmpty()) {
        return false;
    }

    // 开启事务
    db.transaction();

    // 构建插入语句
    QString sql = QString("INSERT INTO %1 (%2) VALUES ").arg(tableName, columns.join(","));
    QString valuesPlaceholder;
    for (int i = 0; i < columns.size(); ++i) {
        valuesPlaceholder += ":col" + QString::number(i) + ",";
    }
    valuesPlaceholder.chop(1);  // 去掉最后一个逗号

    // 准备查询对象
    QSqlQuery query(db);
    query.prepare(sql + " (" + valuesPlaceholder + ")");

    // 批量插入数据
    for (const auto &data : dataList) {
        if (data.size() != columns.size()) {
            qDebug()<<"数据格式不匹配，回滚事务";

            db.rollback();  // 数据格式不匹配，回滚事务
            return false;
        }

        for (int i = 0; i < data.size(); ++i) {
            query.bindValue(":col" + QString::number(i), data[i]);
        }

        if (!query.exec()) {
            qDebug()<<"插入失败，回滚事务";
            qDebug() << "Executed SQL:" << query.lastQuery();

            db.rollback();  // 插入失败，回滚事务
            return false;
        }
    }
    // 提交事务
    return db.commit();
}

bool MySql::update(const QString &tableName, const QHash<QString, QVariant> &data, const QString &where)
{
    QString updates;
    for (auto it = data.cbegin(); it != data.cend(); ++it) {
        updates += it.key() + "=:" + it.key() + ",";
    }
    updates.chop(1);

    QString sql = QString("UPDATE %1 SET %2 WHERE %3").arg(tableName, updates, where);
    QSqlQuery query;
    query.prepare(sql);
    for (auto it = data.cbegin(); it != data.cend(); ++it) {
        query.bindValue(":" + it.key(), it.value());
    }
    return query.exec();
}

bool MySql::del(const QString &tableName, const QString &where)
{
    QString sql = QString("DELETE FROM %1 WHERE %2").arg(tableName, where);
    return QSqlQuery().exec(sql);
}

QHash<QString, QVariant> MySql::find(const QString &tableName, const QString &where)
{
    QString sql = QString("SELECT * FROM %1 WHERE %2").arg(tableName, where);
    QSqlQuery query(sql);
    QHash<QString, QVariant> result;
    if (query.next()) {
        QSqlRecord record = query.record();
        for (int i = 0; i < record.count(); ++i) {
            result[record.fieldName(i)] = record.value(i);
            qDebug()<<record.fieldName(i)<<":"<<record.value(i);

        }
    }
    return result;
}

QList<QHash<QString, QVariant>> MySql::select(const QString &fields,const QString &tableName, const QString &where)
{
    QString sql = QString("SELECT %1 FROM %2 WHERE %3").arg(fields,tableName, where);
    QSqlQuery query(sql);
    qDebug()<<"sql"<<sql<<lastError();

    QList<QHash<QString, QVariant>> results;
    while (query.next()) {
        QHash<QString, QVariant> result;
        QSqlRecord record = query.record();
        for (int i = 0; i < record.count(); ++i) {
            result[record.fieldName(i)] = record.value(i);
        }
        results.append(result);
    }
    return results;
}

QString MySql::lastError() const
{
    return db.lastError().text();

}
